package com.eascs.banknet.boc.dao;

import java.util.List;
import org.springframework.stereotype.Repository;
import com.eascs.banknet.boc.vo.bocB01.BOCB01Bean;
import com.eascs.banknet.common.dao.BaseO2oDao;

@Repository("bocB01Dao")
public class BOCB01Dao extends BaseO2oDao{
	public List<BOCB01Bean> queryData(String startId){
		StringBuffer sql = new StringBuffer("select * from (");
		sql.append("SELECT t2.loanapply_no 'loanapplyNo',t1.bill_code 'billCode',t2.id 'loanUserId',t2.spouse_id 'spouseId',t2.sponsor_type 'sponsorType', ")
		.append(" user_surname 'CCTPERTRN_PERLSTNM',user_name 'CCTPERTRN_PERFSTNM',document_type 'CCTPERTRN_CBICERTY',document_no 'CCTPERTRN_CBICERNO' ")
		.append(" FROM o2o_loanapply t1,o2o_loan_user t2 ")
		.append(" WHERE t1.loanapply_no = t2.loanapply_no and (t1.data_source is null or t1.data_source!='2') AND t1.islock = 'N' AND t1.bank_type = 'BC' AND t1.wf_type = 'Z' and t2.sponsor_type is null and t2.spouse_id is null ")
		.append(" AND EXISTS (SELECT 1 FROM o2o_datalist t3 WHERE t1.loanapply_no = t3.loanapply_no AND t3.datalist_code IN ('IDC') and t3.is_delete = 'N') ")
		.append(" AND EXISTS (SELECT 1 FROM o2o_datalist t3 WHERE t1.loanapply_no = t3.loanapply_no AND t3.datalist_code IN ('CRD') and t3.is_delete = 'N') ")
		.append(" AND EXISTS (SELECT 1 FROM o2o_datalist t3 WHERE t1.loanapply_no = t3.loanapply_no AND t3.datalist_code IN ('IDRC') and t3.is_delete = 'N') ")
		.append(" AND (t2.bank_status IS NULL OR t2.bank_applyno IS NULL) ")
		.append(" union all ")
		.append(" SELECT t2.loanapply_no 'loanapplyNo',t1.bill_code 'billCode',t2.id 'loanUserId',t2.spouse_id 'spouseId',t2.sponsor_type 'sponsorType', ")
		.append(" user_surname 'CCTPERTRN_PERLSTNM',user_name 'CCTPERTRN_PERFSTNM',document_type 'CCTPERTRN_CBICERTY',document_no 'CCTPERTRN_CBICERNO' ")
		.append(" FROM o2o_loanapply t1,o2o_loan_user t2 ")
		.append(" WHERE t1.loanapply_no = t2.loanapply_no and (t1.data_source is null or t1.data_source!='2') AND t1.islock = 'N' AND t1.bank_type = 'BC' AND t1.wf_type = 'Z' and t2.sponsor_type is null and t2.spouse_id is not null ")
		.append(" AND EXISTS (SELECT 1 FROM o2o_datalist t3 WHERE t1.loanapply_no = t3.loanapply_no AND t3.datalist_code IN ('MATE_IDC') and t3.is_delete = 'N') ")
		.append(" AND EXISTS (SELECT 1 FROM o2o_datalist t3 WHERE t1.loanapply_no = t3.loanapply_no AND t3.datalist_code IN ('MATE_CRD') and t3.is_delete = 'N') ")
		.append(" AND EXISTS (SELECT 1 FROM o2o_datalist t3 WHERE t1.loanapply_no = t3.loanapply_no AND t3.datalist_code IN ('MATE_IDRC') and t3.is_delete = 'N') ")
		.append(" AND (t2.bank_status IS NULL OR t2.bank_applyno IS NULL) ")
		.append(" union all ")
		.append(" SELECT t2.loanapply_no 'loanapplyNo',t1.bill_code 'billCode',t2.id 'loanUserId',t2.spouse_id 'spouseId',t2.sponsor_type 'sponsorType', ")
		.append(" user_surname 'CCTPERTRN_PERLSTNM',user_name 'CCTPERTRN_PERFSTNM',document_type 'CCTPERTRN_CBICERTY',document_no 'CCTPERTRN_CBICERNO' ")
		.append(" FROM o2o_loanapply t1,o2o_loan_user t2 ")
		.append(" WHERE t1.loanapply_no = t2.loanapply_no and (t1.data_source is null or t1.data_source!='2') AND t1.islock = 'N' AND t1.bank_type = 'BC'  and t1.wf_type != 'T' ")
		.append(" and t2.sponsor_type = 'D' and t2.sponsor_permit_credit = 'Y' AND (t2.bank_status IS NULL OR t2.bank_applyno IS NULL) ")
		.append(" and not EXISTS (SELECT 1 FROM o2o_datalist t3 WHERE t1.loanapply_no = t3.loanapply_no and t2.id = t3.sponsor_id AND t3.datalist_code = 'SPONSOR_ZXBG' and t3.is_delete = 'N' )")
		.append(" ) t ORDER BY t.billCode LIMIT 1");
		return super.findEntitys(sql.toString(), BOCB01Bean.class);
	};
	
	public int updateInfo(String loanUserId, String appno){
		String sql = "UPDATE o2o_loan_user SET bank_applyno = ?, bank_status='1' WHERE id = ?";
		return super.execute(sql, appno, loanUserId);
	}
}
